package library.persistence.impl;

import library.domain.User;
import library.persistence.dao.AccountDao;
import library.persistence.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class AccountDaoImpl implements AccountDao {

    public static final String GET_ACCOUNT_BY_USERNAME="SELECT password,studentid,ismanager FROM userinfo WHERE username=?";
    public static final String GET_ACCOUNT_BY_USERNAME_AND_PASSWORD="SELECT studentid,ismanager FROM userinfo WHERE username=? AND password=?";
    public static final String INSERT_ACCOUNT= "INSERT INTO userinfo(username,password,studentid,ismanager) VALUES (?,?,?,?)";
    public static final String UPDATE_ACCOUNT_BY_USERNAME="UPDATE userinfo SET password=?,studentid=?,ismanager=? WHERE username=?";



    @Override
    public User getAccountByUsername(String username) {
        User user = new User();
        Connection connection = DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ACCOUNT_BY_USERNAME);
            preparedStatement.setString(1,username);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                user.setUsername(username);
                user.setPassword(resultSet.getString(1));
                user.setStudentId(resultSet.getString(2));
                user.setIsManager(resultSet.getInt(3));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public User getAccountByUsernameAndPassword(String username, String password) {
        User user = new User();
        Connection connection = DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ACCOUNT_BY_USERNAME_AND_PASSWORD);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                user.setUsername(username);
                user.setPassword(password);
                user.setStudentId(resultSet.getString(1));
                user.setIsManager(resultSet.getInt(2));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;

    }

    @Override
    public void insertAccount(User user) {
        Connection connection = DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_ACCOUNT);
            preparedStatement.setString(1,user.getUsername());
            preparedStatement.setString(2,user.getPassword());
            preparedStatement.setString(3,user.getStudentId());
            preparedStatement.setInt(4,user.getIsManager());
            preparedStatement.execute();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateAccount(User user) {
        Connection connection = DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_ACCOUNT_BY_USERNAME);
            preparedStatement.setString(1,user.getPassword());
            preparedStatement.setString(2,user.getStudentId());
            preparedStatement.setInt(3,user.getIsManager());
            preparedStatement.setString(4,user.getUsername());
            preparedStatement.executeUpdate();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


//    public static void main(String[] args) {
//        AccountDao accountDao = new AccountDaoImpl();
//        User user1 = accountDao.getAccountByUsername("张三");
//
//        System.out.println("用户名："+user1.getUsername()+" 用户密码："+user1.getPassword()+" 用户学号:"+user1.getStudentId()+" 用户是否是管理员:"+user1.getIsManager());
//
//        User user2 = new User("李四","123456","8209210200",1);
//
//        accountDao.updateAccount(user2);
//        User user3 = accountDao.getAccountByUsername(user2.getUsername());
//        System.out.println("用户名："+user3.getUsername()+" 用户密码："+user3.getPassword()+" 用户学号:"+user3.getStudentId()+" 用户是否是管理员:"+user3.getIsManager());
//
//
//    }

}
